﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CalendarMgm.Models;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace CalendarMgm.Models
{
    public class CalendarDB : ICalendarDB
    {
        private MySqlConnection conn;
        private MySqlCommand com;
        private MySqlDataReader reader;

        // 构造函数
        public CalendarDB()
        {
            // 创建数据库连接
            try
            {
                reader = null;
                com = null;
                conn = new MySqlConnection("server=localhost;user id=root;password=ljegege;database=cpt");
            }
            catch (MySqlException e)
            {

            }
        }
        // 通过用户ID登录。 返回0则账号错误，返回1则成功，返回2则密码错误，返回3则数据库异常，不能登录,返回4则密码长度不对。
        public int login(int id, string password)
        {
            int isSuccess = 0;
            if (password.Length == 40)
            {
                try
                {
                    // 打开连接，通过用户ID获取数据库
                    conn.Open();
                    string query = "select * from user where userID=" + id;//有问题的
                    com = new MySqlCommand(query, conn);
                    reader = com.ExecuteReader();

                    if (reader.Read())
                    {
                        //  登录成功
                        if (reader["userPsw"].ToString().CompareTo(password) == 0)
                        {
                            // 释放数据库资源
                            isSuccess = 1;
                        }
                        // 密码错误
                        else
                        {
                            isSuccess = 2;
                        }
                    }
                }
                // 数据库异常
                catch (MySqlException e)
                {
                    //  考虑如何处理异常
                    isSuccess = 3;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (com != null)
                    {
                        com.Dispose();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            else // 密码长度不对
            {
                isSuccess = 4;
            }
            return isSuccess;
        }

        // 通过email登录。 返回0则账号错误，返回1则成功，返回2则密码错误，返回3则数据库异常，不能登录,返回4则密码长度不对。
        public int login(string email, string password)
        {
            int isSuccess = 0;
            if (password.Length == 40)
            {
                try
                {
                    // 打开连接，通过用户ID获取数据库
                    conn.Open();
                    string query = "select * from user where userEmail='" + email + "'";//有问题的
                    com = new MySqlCommand(query, conn);
                    reader = com.ExecuteReader();

                    if (reader.Read())
                    {
                        //  登录成功
                        if (reader["userPsw"].ToString().CompareTo(password) == 0)
                        {
                            // 释放数据库资源
                            isSuccess = 1;
                        }
                        // 密码错误
                        else
                        {
                            isSuccess = 2;
                        }
                    }
                }
                // 数据库异常
                catch (MySqlException e)
                {
                    //  考虑如何处理异常
                    isSuccess = 3;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (com != null)
                    {
                        com.Dispose();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            else// 密码长度不对
            {
                isSuccess = 4;
            }
            return isSuccess;
        }
        
        // 注册成功就返回用户ID，email已经被注册则返回-1，数据库异常则返回-2
        public int register(string userName, string passord, string userEmail, string sex, string province, string city)
        {
            int result = -1;
            try
            {
                // 验证用户Email
                conn.Open();
                string query = "select * from user where userEmail='" + userEmail + "'";
                com = new MySqlCommand(query, conn);
                reader = com.ExecuteReader();
                // 此email已经被注册
                if (reader.HasRows)
                {
                    result = -1;
                }
                else
                {
                    reader.Close();

                    // 将用户信息写进数据库
                    query = "insert into user(userName,userPsw,userEmail,sex,province,city) values('" + userName + "','" + passord + "','" + userEmail + "','" + sex + "','" + province + "','"  + city +"')";
                    com = new MySqlCommand(query, conn);
                    com.ExecuteNonQuery();

                    //  读取用户注册的ID
                    com.CommandText = "select last_insert_id() from user limit 1 ";
                    reader = com.ExecuteReader();
                    if (reader.Read())
                    {
                        // 有问题
                        string tempID = reader[0].ToString().Trim();
                        result = Int32.Parse(tempID);
                    }
                }

            }
            // 数据库异常
            catch (MySqlException e)
            {
                //  考虑如何处理异常
                result = -2;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (com != null)
                {
                    com.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return result;
        }
        
        // 获取用户信息User为引用参数，可直接进行修改
        public bool getUserInfo(CalendarMgm.util.User user)
        {

            // 获取用户信息
            conn.Open();
            string query = "select * from user where userID=" + user.ID + " and userPsw='" + user.password + "'";
            com = new MySqlCommand(query, conn);
            reader = com.ExecuteReader();
            if (reader.Read())
            {
                user.name = reader["userName"].ToString();
                user.email = reader["userEmail"].ToString();
                user.sex = reader["sex"].ToString();
                user.province = reader["province"].ToString();
                user.city = reader["city"].ToString();

                return true;
            }
            else
            {
                return false;
            }
        }

    }
}